package com.barry;

import com.barry.bean.OperationData;
import com.barry.bean.Row;
import com.barry.bean.Worksheet;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.antlr.stringtemplate.StringTemplate;
import org.antlr.stringtemplate.StringTemplateGroup;

import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * 类功能描述：generator big data Excel
 *
 * @author WangXueXing create at 19-4-13 下午10:23
 * @version 1.0.0
 */
@Slf4j
public class ExcelGenerator {
    public final static int ONE_SHEET_LIMIT_ROW = 100000;
    public final static int ONE_WRITE_ROW = 20000;

    public static void main(String[] args) throws FileNotFoundException{
        List<OperationData> list = genDataList(2, 20, 10);
        System.out.println(list);
/*        ExcelGenerator template = new ExcelGenerator();
        template.output2();*/
       /* long startTimne = System.currentTimeMillis();
        File file = new File("C:\\data\\bak\\output"+startTimne+".xlsx");
        List<List<OperationData>> list = genData();
        long endTime1 = System.currentTimeMillis();
        System.out.println("用时="+((endTime1-startTimne)/1000)+"秒");
        ExcelGenerator.writeExcelOneSheetByList("operation_data_",
                new FileOutputStream(file),
                "经营数据",
                OperationData.class,
                list);*/
    }

    public static List<List<OperationData>> genData(){
        long startTimne = System.currentTimeMillis();
        List<OperationData> dataList = Lists.newArrayList();
        String prefix = "1000";
        for(int i=0; i<ONE_SHEET_LIMIT_ROW; i++){
            int val = (int)(Math.random()*10+1);
            OperationData operationData = new OperationData();
            operationData.setAvgMakeLoanAm(prefix+val).setBackOrderAmount(prefix+val).setBadCardNumber(prefix+val)
                    .setCardPeriod(prefix+val).setConPg(prefix+val)
                    .setConPg90(prefix+val).setEndInventAm(prefix+val).setEndInventAm(prefix+val)
                    .setFactFee(prefix+val).setFee(prefix+val).setFirstContYear(prefix+val).setLossNum(prefix+val)
                    .setGroupNumber(prefix+val).setCardNumber(prefix+val)
                    .setMakeLoanInt(prefix+val).setMakeLoanNum(prefix+val)
                    .setNetPg(prefix+val).setOrderAmount(prefix+val).setOverdueInt(prefix+val)
                    .setMakeLoanAm(prefix+val).setOverdueNum(prefix+val)
                    .setOverdueNum(prefix+val).setSaleAmount(prefix+val).setReceiveOrderAmount(prefix+val)
                    .setSaleAmount90(prefix+val).setSaleAmount180(prefix+val)
                    .setSaleRecord(prefix+val).setSupplierName(prefix+val)
                    .setOrderNumber(prefix+val).setReceiveRecord(prefix+val);
            dataList.add(operationData);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
        long startTimne1 = System.currentTimeMillis();
        int n = ONE_SHEET_LIMIT_ROW/ONE_WRITE_ROW;
        List<List<OperationData>> list = ExcelGenerator.averageAssignList(dataList, n);
        long endTime1 = System.currentTimeMillis();
        System.out.println("用时="+((endTime1-startTimne1)/1000)+"秒");
        return list;
    }

    public static List<OperationData> genDataList(int currentPage, int pageSize, int count){
        long startTimne = System.currentTimeMillis();
        List<OperationData> dataList = Lists.newArrayList();
        String prefix = "1000";
        for(int i=0; i<count; i++){
            int val = (currentPage+1)*pageSize+(i+1);
            OperationData operationData = new OperationData();
            operationData.setAvgMakeLoanAm(prefix+val).setBackOrderAmount(prefix+val).setBadCardNumber(prefix+val)
                    .setCardPeriod(prefix+val).setConPg(prefix+val)
                    .setConPg90(prefix+val).setEndInventAm(prefix+val).setEndInventAm(prefix+val)
                    .setFactFee(prefix+val).setFee(prefix+val).setFirstContYear(prefix+val).setLossNum(prefix+val)
                    .setGroupNumber(prefix+val).setCardNumber(prefix+val)
                    .setMakeLoanInt(prefix+val).setMakeLoanNum(prefix+val)
                    .setNetPg(prefix+val).setOrderAmount(prefix+val).setOverdueInt(prefix+val)
                    .setMakeLoanAm(prefix+val).setOverdueNum(prefix+val)
                    .setOverdueNum(prefix+val).setSaleAmount(prefix+val).setReceiveOrderAmount(prefix+val)
                    .setSaleAmount90(prefix+val).setSaleAmount180(prefix+val)
                    .setSaleRecord(prefix+val).setSupplierName(prefix+val)
                    .setOrderNumber(prefix+val).setReceiveRecord(prefix+val);
            dataList.add(operationData);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
        return dataList;
    }

    /**
     * 生成数据量大的时候，该方法会出现内存溢出
     * @throws FileNotFoundException
     */
    public void output1() throws FileNotFoundException{
        StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
        StringTemplate st4 =  stGroup.getInstanceOf("template/test");
        List<Worksheet> worksheets = new ArrayList<>();

        File file = new File("C:\\data\\bak\\output.xls");
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));

        for(int i=0;i<30;i++){
            Worksheet worksheet = new Worksheet();
            worksheet.setSheet("第"+(i+1)+"页");
            List<Row> rows = new ArrayList<>();
            for(int j=0;j<6000;j++){
                Row row = new Row();
                row.setName1("zhangzehao");
                row.setName2(""+j);
                row.setName3(i+" "+j);
                rows.add(row);
            }
            worksheet.setRows(rows);
            worksheets.add(worksheet);
        }

        st4.setAttribute("worksheets", worksheets);
        writer.write(st4.toString());
        writer.flush();
        writer.close();
        System.out.println("生成excel完成");
    }

    /**
     * 该方法不管生成多大的数据量，都不会出现内存溢出，只是时间的长短
     * 经测试，生成1800万数据，6~10分钟之间，3G大的文件，打开大文件就看内存是否足够大了
     * 数据量小的时候，推荐用jxls的模板技术生成excel文件，谁用谁知道，大数据量可以结合该方法使用
     * @throws FileNotFoundException
     */
    public void output2() throws FileNotFoundException{
        long startTimne = System.currentTimeMillis();
        StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

        //写入excel文件头部信息
        StringTemplate head =  stGroup.getInstanceOf("template/head");
        File file = new File("C:\\data\\bak\\output.xls");
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
        writer.print(head.toString());
        writer.flush();

        int sheets = 2;
        //excel单表最大行数是65535
        int maxRowNum = 60000;

        //写入excel文件数据信息
        for(int i=0;i<sheets;i++){
            StringTemplate body =  stGroup.getInstanceOf("template/body");
            Worksheet worksheet = new Worksheet();
            worksheet.setSheet(" "+(i+1)+" ");
            worksheet.setColumnNum(3);
            worksheet.setRowNum(maxRowNum);
            List<Row> rows = new ArrayList<>();
            for(int j=0;j<maxRowNum;j++){
                Row row = new Row();
                row.setName1(""+new Random().nextInt(100000));
                row.setName2(""+j);
                row.setName3(i+""+j);
                rows.add(row);
            }
            worksheet.setRows(rows);
            body.setAttribute("worksheet", worksheet);
            writer.print(body.toString());
            writer.flush();
            rows.clear();
            rows = null;
            worksheet = null;
            body = null;
            Runtime.getRuntime().gc();
            //System.out.println("正在生成excel文件的 sheet"+(i+1));
        }

        //写入excel文件尾部
        writer.print("</Workbook>");
        writer.flush();
        writer.close();
        System.out.println("生成excel文件完成");
        long endTime = System.currentTimeMillis();
        System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
    }

    /**
     * 写入单个Sheet的Excel
     * @param templatePrefix 模板前缀，默认两个模板后缀分别为head及body
     * @param outFile 生成Excel文件
     * @param sheetName 单个sheet名称
     * @param dataList 填充数据列表
     * @param <T> 填充对象泛型
     * @throws FileNotFoundException
     * @throws ClassNotFoundException
     */
    public static <T> void writeExcelOneSheet(String templatePrefix, File outFile, String sheetName, Class clazz, List<T> dataList){
        long startTimne = System.currentTimeMillis();
        StringTemplateGroup stGroup = new StringTemplateGroup(String.valueOf(startTimne));

        //写入excel文件头部信息
        StringTemplate head =  stGroup.getInstanceOf("template"+File.separator+templatePrefix+"head");
        try(PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(outFile)))) {
            writer.print(head.toString());
            writer.flush();

            //excel单表最大行数是65535
            Field[] fields = clazz.getDeclaredFields();
            //写入excel文件数据信息
            StringTemplate body =  stGroup.getInstanceOf("template"+File.separator+templatePrefix+"body");
            Worksheet worksheet = new Worksheet();
            worksheet.setSheet(sheetName);
            worksheet.setColumnNum(fields.length);
            worksheet.setRowNum(ONE_SHEET_LIMIT_ROW);
            worksheet.setRows(dataList);
            body.setAttribute("worksheet", worksheet);
            writer.print(body.toString());
            writer.flush();
            //写入excel文件尾部
            writer.print("</Workbook>");
            writer.flush();
            long endTime = System.currentTimeMillis();
            System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
        } catch (Exception e) {
            log.error("写入Excel异常", e);
        }
    }


    /**
     * 写入单个Sheet的Excel
     * @param templatePrefix 模板前缀，默认两个模板后缀分别为head及body
     * @param outputStream 生成Excel文件
     * @param sheetName 单个sheet名称
     * @param dataList 填充数据列表
     * @param <T> 填充对象泛型
     * @throws FileNotFoundException
     * @throws ClassNotFoundException
     */
    public static <T> void writeExcelOneSheetByList(String templatePrefix,
                                                    OutputStream outputStream,
                                                    String sheetName,
                                                    Class clazz,
                                                    List<List<T>> dataList){
        long startTimne = System.currentTimeMillis();
        StringTemplateGroup stGroup = new StringTemplateGroup(String.valueOf(startTimne));
        try(PrintWriter writer = new PrintWriter(new BufferedOutputStream(outputStream))) {
            //写入excel文件头部信息
            StringTemplate head =  stGroup.getInstanceOf("template"+File.separator+templatePrefix+"head");
            writer.print(head.toString());
            writer.flush();
            //excel单表最大行数是65535
            Field[] fields = clazz.getDeclaredFields();
            dataList.forEach(x->{
                long startTimne1 = System.currentTimeMillis();
                //写入excel文件数据信息
                StringTemplate body =  stGroup.getInstanceOf("template"+File.separator+templatePrefix+"body");
                Worksheet worksheet = new Worksheet();
                worksheet.setSheet(sheetName);
                worksheet.setColumnNum(fields.length);
                worksheet.setRowNum(ONE_SHEET_LIMIT_ROW);
                worksheet.setRows(x);
                body.setAttribute("worksheet", worksheet);
                writer.print(body.toString());
                writer.flush();
                long endTime1 = System.currentTimeMillis();
                System.out.println("用时="+((endTime1-startTimne1)/1000)+"秒");
            });
            //写入excel文件头部信息
            StringTemplate foot =  stGroup.getInstanceOf("template"+File.separator+templatePrefix+"foot");
            writer.print(foot.toString());
            writer.flush();
        } catch (Exception e) {
            log.error("写入Excel异常", e);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("用时="+((endTime-startTimne)/1000)+"秒");

    }

    /**
     * 将一个list均分成n个list,主要通过偏移量来实现的
     * @param source
     * @return
     */
    public static <T> List<List<T>> averageAssignList(List<T> source, int n) {
        List<List<T>> result = new ArrayList<List<T>>();
        int remaider = source.size() % n;  //(先计算出余数)
        int number = source.size() / n;  //然后是商
        int offset = 0;//偏移量
        for (int i = 0; i < n; i++) {
            List<T> value = null;
            if (remaider > 0) {
                value = source.subList(i * number + offset, (i + 1) * number + offset + 1);
                remaider--;
                offset++;
            } else {
                value = source.subList(i * number + offset, (i + 1) * number + offset);
            }
            result.add(value);
        }
        return result;
    }
}